/*
 * Copyright 2004-2020 H2 Group. Multiple-Licensed under the MPL 2.0,
 * and the EPL 1.0 (https://h2database.com/html/license.html).
 * Initial Developer: H2 Group
 */
package org.h2.server.web;

import java.io.ByteArrayOutputStream;
import java.io.PrintStream;
import java.io.PrintWriter;
import java.io.StringReader;
import java.io.StringWriter;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Locale;
import java.util.Map;
import java.util.Properties;
import java.util.Random;

import org.h2.api.ErrorCode;
import org.h2.bnf.Bnf;
import org.h2.bnf.context.DbColumn;
import org.h2.bnf.context.DbContents;
import org.h2.bnf.context.DbSchema;
import org.h2.bnf.context.DbTableOrView;
import org.h2.command.Parser;
import org.h2.engine.Constants;
import org.h2.engine.SysProperties;
import org.h2.jdbc.JdbcException;
import org.h2.message.DbException;
import org.h2.security.SHA256;
import org.h2.tools.Backup;
import org.h2.tools.ChangeFileEncryption;
import org.h2.tools.ConvertTraceFile;
import org.h2.tools.CreateCluster;
import org.h2.tools.DeleteDbFiles;
import org.h2.tools.Recover;
import org.h2.tools.Restore;
import org.h2.tools.RunScript;
import org.h2.tools.Script;
import org.h2.tools.SimpleResultSet;
import org.h2.util.JdbcUtils;
import org.h2.util.NetUtils;
import org.h2.util.NetworkConnectionInfo;
import org.h2.util.Profiler;
import org.h2.util.ScriptReader;
import org.h2.util.SortedProperties;
import org.h2.util.StringUtils;
import org.h2.util.Tool;
import org.h2.util.Utils;
import org.h2.value.DataType;

/**
 * For each connection to a session, an object of this class is created.
 * This class is used by the H2 Console.
 */
public class WebApp {

    private static final Comparator<DbTableOrView> SYSTEM_SCHEMA_COMPARATOR = Comparator
            .comparing(DbTableOrView::getName, String.CASE_INSENSITIVE_ORDER);

    /**
     * The web server.
     */
    protected final WebServer server;

    /**
     * The session.
     */
    protected WebSession session;

    /**
     * The session attributes
     */
    protected Properties attributes;

    /**
     * The mime type of the current response.
     */
    protected String mimeType;

    /**
     * Whether the response can be cached.
     */
    protected boolean cache;

    /**
     * Whether to close the connection.
     */
    protected boolean stop;

    /**
     * The language in the HTTP header.
     */
    protected String headerLanguage;

    private Profiler profiler;

    WebApp(WebServer server) {
        this.server = server;
    }

    /**
     * Set the web session and attributes.
     *
     * @param session the session
     * @param attributes the attributes
     */
    void setSession(WebSession session, Properties attributes) {
        this.session = session;
        this.attributes = attributes;
    }

    /**
     * Process an HTTP request.
     *
     * @param file the file that was requested
     * @param networkConnectionInfo the network connection information
     * @return the name of the file to return to the client
     */
    String processRequest(String file, NetworkConnectionInfo networkConnectionInfo) {
        int index = file.lastIndexOf('.');
        String suffix;
        if (index >= 0) {
            suffix = file.substring(index + 1);
        } else {
            suffix = "";
        }
        if ("ico".equals(suffix)) {
            mimeType = "image/x-icon";
            cache = true;
        } else if ("gif".equals(suffix)) {
            mimeType = "image/gif";
            cache = true;
        } else if ("css".equals(suffix)) {
            cache = true;
            mimeType = "text/css";
        } else if ("html".equals(suffix) ||
                "do".equals(suffix) ||
                "jsp".equals(suffix)) {
            cache = false;
            mimeType = "text/html";
            if (session == null) {
                session = server.createNewSession(
                        NetUtils.ipToShortForm(null, networkConnectionInfo.getClientAddr(), false).toString());
                if (!"notAllowed.jsp".equals(file)) {
                    file = "index.do";
                }
            }
        } else if ("js".equals(suffix)) {
            cache = true;
            mimeType = "text/javascript";
        } else {
            cache = true;
            mimeType = "application/octet-stream";
        }
        trace("mimeType=" + mimeType);
        trace(file);
        if (file.endsWith(".do")) {
            file = process(file, networkConnectionInfo);
        } else if (file.endsWith(".jsp")) {
            switch (file) {
            case "admin.jsp":
            case "tools.jsp":
                if (!checkAdmin(file)) {
                    file = process("adminLogin.do", networkConnectionInfo);
                }
            }
        }
        return file;
    }

    private static String getComboBox(String[] elements, String selected) {
        StringBuilder buff = new StringBuilder();
        for (String value : elements) {
            buff.append("<option value=\"").
                append(PageParser.escapeHtmlData(value)).
                append('\"');
            if (value.equals(selected)) {
                buff.append(" selected");
            }
            buff.append('>').
                append(PageParser.escapeHtml(value)).
                append("</option>");
        }
        return buff.toString();
    }

    private static String getComboBox(String[][] elements, String selected) {
        StringBuilder buff = new StringBuilder();
        for (String[] n : elements) {
            buff.append("<option value=\"").
                append(PageParser.escapeHtmlData(n[0])).
                append('\"');
            if (n[0].equals(selected)) {
                buff.append(" selected");
            }
            buff.append('>').
                append(PageParser.escapeHtml(n[1])).
                append("</option>");
        }
        return buff.toString();
    }

    private String process(String file, NetworkConnectionInfo networkConnectionInfo) {
        trace("process " + file);
        while (file.endsWith(".do")) {
            switch (file) {
            case "login.do":
                file = login(networkConnectionInfo);
                break;
            case "index.do":
                file = index();
                break;
            case "logout.do":
                file = logout();
                break;
            case "settingRemove.do":
                file = settingRemove();
                break;
            case "settingSave.do":
                file = settingSave();
                break;
            case "test.do":
                file = test(networkConnectionInfo);
                break;
            case "query.do":
                file = query();
                break;
            case "tables.do":
                file = tables();
                break;
            case "editResult.do":
                file = editResult();
                break;
            case "getHistory.do":
                file = getHistory();
                break;
            case "admin.do":
                file = checkAdmin(file) ? admin() : "adminLogin.do";
                break;
            case "adminSave.do":
                file = checkAdmin(file) ? adminSave() : "adminLogin.do";
                break;
            case "adminStartTranslate.do":
                file = checkAdmin(file) ? adminStartTranslate() : "adminLogin.do";
                break;
            case "adminShutdown.do":
                file = checkAdmin(file) ? adminShutdown() : "adminLogin.do";
                break;
            case "autoCompleteList.do":
                file = autoCompleteList();
                break;
            case "tools.do":
                file = checkAdmin(file) ? tools() : "adminLogin.do";
                break;
            case "adminLogin.do":
                file = adminLogin();
                break;
            default:
                file = "error.jsp";
                break;
            }
        }
        trace("return " + file);
        return file;
    }

    private boolean checkAdmin(String file) {
        Boolean b = (Boolean) session.get("admin");
        if (b != null && b) {
            return true;
        }
        String key = server.getKey();
        if (key != null && key.equals(session.get("key"))) {
            return true;
        }
        session.put("adminBack", file);
        return false;
    }

    private String adminLogin() {
        String password = attributes.getProperty("password");
        if (password == null || password.isEmpty() || !server.checkAdminPassword(password)) {
            return "adminLogin.jsp";
        }
        String back = (String) session.remove("adminBack");
        session.put("admin", true);
        return back != null ? back : "admin.do";
    }

    private String autoCompleteList() {
        String query = (String) attributes.get("query");
        boolean lowercase = false;
        String tQuery = query.trim();
        if (!tQuery.isEmpty() && Character.isLowerCase(tQuery.charAt(0))) {
            lowercase = true;
        }
        try {
            String sql = query;
            if (sql.endsWith(";")) {
                sql += " ";
            }
            ScriptReader reader = new ScriptReader(new StringReader(sql));
            reader.setSkipRemarks(true);
            String lastSql = "";
            while (true) {
                String n = reader.readStatement();
                if (n == null) {
                    break;
                }
                lastSql = n;
            }
            String result = "";
            if (reader.isInsideRemark()) {
                if (reader.isBlockRemark()) {
                    result = "1#(End Remark)# */\n" + result;
                } else {
                    result = "1#(Newline)#\n" + result;
                }
            } else {
                sql = lastSql;
                while (sql.length() > 0 && sql.charAt(0) <= ' ') {
                    sql = sql.substring(1);
                }
                String tSql = sql.trim();
                if (!tSql.isEmpty() && Character.isLowerCase(tSql.charAt(0))) {
                    lowercase = true;
                }
                Bnf bnf = session.getBnf();
                if (bnf == null) {
                    return "autoCompleteList.jsp";
                }
                HashMap<String, String> map = bnf.getNextTokenList(sql);
                String space = "";
                if (sql.length() > 0) {
                    char last = sql.charAt(sql.length() - 1);
                    if (!Character.isWhitespace(last) && (last != '.' &&
                            last >= ' ' && last != '\'' && last != '"')) {
                        space = " ";
                    }
                }
                ArrayList<String> list = new ArrayList<>(map.size());
                for (Map.Entry<String, String> entry : map.entrySet()) {
                    String key = entry.getKey();
                    String value = entry.getValue();
                    String type = String.valueOf(key.charAt(0));
                    if (Integer.parseInt(type) > 2) {
                        continue;
                    }
                    key = key.substring(2);
                    if (Character.isLetter(key.charAt(0)) && lowercase) {
                        key = StringUtils.toLowerEnglish(key);
                        value = StringUtils.toLowerEnglish(value);
                    }
                    if (key.equals(value) && !".".equals(value)) {
                        value = space + value;
                    }
                    key = StringUtils.urlEncode(key);
                    key = key.replace('+', ' ');
                    value = StringUtils.urlEncode(value);
                    value = value.replace('+', ' ');
                    list.add(type + "#" + key + "#" + value);
                }
                Collections.sort(list);
                if (query.endsWith("\n") || tQuery.endsWith(";")) {
                    list.add(0, "1#(Newline)#\n");
                }
                result = StringUtils.join(new StringBuilder(), list, "|").toString();
            }
            session.put("autoCompleteList", result);
        } catch (Throwable e) {
            server.traceError(e);
        }
        return "autoCompleteList.jsp";
    }

    private String admin() {
        session.put("port", Integer.toString(server.getPort()));
        session.put("allowOthers", Boolean.toString(server.getAllowOthers()));
        session.put("ssl", String.valueOf(server.getSSL()));
        session.put("sessions", server.getSessions());
        return "admin.jsp";
    }

    private String adminSave() {
        try {
            Properties prop = new SortedProperties();
            int port = Integer.decode((String) attributes.get("port"));
            prop.setProperty("webPort", Integer.toString(port));
            server.setPort(port);
            boolean allowOthers = Utils.parseBoolean((String) attributes.get("allowOthers"), false, false);
            prop.setProperty("webAllowOthers", String.valueOf(allowOthers));
            server.setAllowOthers(allowOthers);
            boolean ssl = Utils.parseBoolean((String) attributes.get("ssl"), false, false);
            prop.setProperty("webSSL", String.valueOf(ssl));
            server.setSSL(ssl);
            byte[] adminPassword = server.getAdminPassword();
            if (adminPassword != null) {
                prop.setProperty("webAdminPassword", StringUtils.convertBytesToHex(adminPassword));
            }
            server.saveProperties(prop);
        } catch (Exception e) {
            trace(e.toString());
        }
        return admin();
    }

    private String tools() {
        try {
            String toolName = (String) attributes.get("tool");
            session.put("tool", toolName);
            String args = (String) attributes.get("args");
            String[] argList = StringUtils.arraySplit(args, ',', false);
            Tool tool = null;
            if ("Backup".equals(toolName)) {
                tool = new Backup();
            } else if ("Restore".equals(toolName)) {
                tool = new Restore();
            } else if ("Recover".equals(toolName)) {
                tool = new Recover();
            } else if ("DeleteDbFiles".equals(toolName)) {
                tool = new DeleteDbFiles();
            } else if ("ChangeFileEncryption".equals(toolName)) {
                tool = new ChangeFileEncryption();
            } else if ("Script".equals(toolName)) {
                tool = new Script();
            } else if ("RunScript".equals(toolName)) {
                tool = new RunScript();
            } else if ("ConvertTraceFile".equals(toolName)) {
                tool = new ConvertTraceFile();
            } else if ("CreateCluster".equals(toolName)) {
                tool = new CreateCluster();
            } else {
                throw DbException.getInternalError(toolName);
            }
            ByteArrayOutputStream outBuff = new ByteArrayOutputStream();
            PrintStream out = new PrintStream(outBuff, false, "UTF-8");
            tool.setOut(out);
            try {
                tool.runTool(argList);
                out.flush();
                String o = new String(outBuff.toByteArray(), StandardCharsets.UTF_8);
                String result = PageParser.escapeHtml(o);
                session.put("toolResult", result);
            } catch (Exception e) {
                session.put("toolResult", getStackTrace(0, e, true));
            }
        } catch (Exception e) {
            server.traceError(e);
        }
        return "tools.jsp";
    }

    private String adminStartTranslate() {
        Map<?, ?> p = Map.class.cast(session.map.get("text"));
        @SuppressWarnings("unchecked")
        Map<Object, Object> p2 = (Map<Object, Object>) p;
        String file = server.startTranslate(p2);
        session.put("translationFile", file);
        return "helpTranslate.jsp";
    }

    /**
     * Stop the application and the server.
     *
     * @return the page to display
     */
    protected String adminShutdown() {
        server.shutdown();
        return "admin.jsp";
    }

    private String index() {
        String[][] languageArray = WebServer.LANGUAGES;
        String language = (String) attributes.get("language");
        Locale locale = session.locale;
        if (language != null) {
            if (locale == null || !StringUtils.toLowerEnglish(
                    locale.getLanguage()).equals(language)) {
                locale = new Locale(language, "");
                server.readTranslations(session, locale.getLanguage());
                session.put("language", language);
                session.locale = locale;
            }
        } else {
            language = (String) session.get("language");
        }
        if (language == null) {
            // if the language is not yet known
            // use the last header
            language = headerLanguage;
        }
        session.put("languageCombo", getComboBox(languageArray, language));
        String[] settingNames = server.getSettingNames();
        String setting = attributes.getProperty("setting");
        if (setting == null && settingNames.length > 0) {
            setting = settingNames[0];
        }
        String combobox = getComboBox(settingNames, setting);
        session.put("settingsList", combobox);
        ConnectionInfo info = server.getSetting(setting);
        if (info == null) {
            info = new ConnectionInfo();
        }
        session.put("setting", PageParser.escapeHtmlData(setting));
        session.put("name", PageParser.escapeHtmlData(setting));
        session.put("driver", PageParser.escapeHtmlData(info.driver));
        session.put("url", PageParser.escapeHtmlData(info.url));
        session.put("user", PageParser.escapeHtmlData(info.user));
        return "index.jsp";
    }

    private String getHistory() {
        int id = Integer.parseInt(attributes.getProperty("id"));
        String sql = session.getCommand(id);
        session.put("query", PageParser.escapeHtmlData(sql));
        return "query.jsp";
    }

    private static int addColumns(boolean mainSchema, DbTableOrView table, StringBuilder builder, int treeIndex,
            boolean showColumnTypes, StringBuilder columnsBuilder) {
        DbColumn[] columns = table.getColumns();
        for (int i = 0; columns != null && i < columns.length; i++) {
            DbColumn column = columns[i];
            if (columnsBuilder.length() > 0) {
                columnsBuilder.append(' ');
            }
            columnsBuilder.append(column.getName());
            String col = escapeIdentifier(column.getName());
            String level = mainSchema ? ", 1, 1" : ", 2, 2";
            builder.append("setNode(").append(treeIndex).append(level)
                    .append(", 'column', '")
                    .append(PageParser.escapeJavaScript(column.getName()))
                    .append("', 'javascript:ins(\\'").append(col).append("\\')');\n");
            treeIndex++;
            if (mainSchema && showColumnTypes) {
                builder.append("setNode(").append(treeIndex)
                        .append(", 2, 2, 'type', '")
                        .append(PageParser.escapeJavaScript(column.getDataType()))
                        .append("', null);\n");
                treeIndex++;
            }
        }
        return treeIndex;
    }

    private static String escapeIdentifier(String name) {
        return StringUtils.urlEncode(
                PageParser.escapeJavaScript(name)).replace('+', ' ');
    }

    /**
     * This class represents index information for the GUI.
     */
    static class IndexInfo {

        /**
         * The index name.
         */
        String name;

        /**
         * The index type name.
         */
        String type;

        /**
         * The indexed columns.
         */
        String columns;
    }

    private static int addIndexes(boolean mainSchema, DatabaseMetaData meta,
            String table, String schema, StringBuilder buff, int treeIndex)
            throws SQLException {
        ResultSet rs;
        try {
            rs = meta.getIndexInfo(null, schema, table, false, true);
        } catch (SQLException e) {
            // SQLite
            return treeIndex;
        }
        HashMap<String, IndexInfo> indexMap = new HashMap<>();
        while (rs.next()) {
            String name = rs.getString("INDEX_NAME");
            IndexInfo info = indexMap.get(name);
            if (info == null) {
                int t = rs.getInt("TYPE");
                String type;
                if (t == DatabaseMetaData.tableIndexClustered) {
                    type = "";
                } else if (t == DatabaseMetaData.tableIndexHashed) {
                    type = " (${text.tree.hashed})";
                } else if (t == DatabaseMetaData.tableIndexOther) {
                    type = "";
                } else {
                    type = null;
                }
                if (name != null && type != null) {
                    info = new IndexInfo();
                    info.name = name;
                    type = (rs.getBoolean("NON_UNIQUE") ?
                            "${text.tree.nonUnique}" : "${text.tree.unique}") + type;
                    info.type = type;
                    info.columns = rs.getString("COLUMN_NAME");
                    indexMap.put(name, info);
                }
            } else {
                info.columns += ", " + rs.getString("COLUMN_NAME");
            }
        }
        rs.close();
        if (indexMap.size() > 0) {
            String level = mainSchema ? ", 1, 1" : ", 2, 1";
            String levelIndex = mainSchema ? ", 2, 1" : ", 3, 1";
            String levelColumnType = mainSchema ? ", 3, 2" : ", 4, 2";
            buff.append("setNode(").append(treeIndex).append(level)
                    .append(", 'index_az', '${text.tree.indexes}', null);\n");
            treeIndex++;
            for (IndexInfo info : indexMap.values()) {
                buff.append("setNode(").append(treeIndex).append(levelIndex)
                        .append(", 'index', '")
                        .append(PageParser.escapeJavaScript(info.name))
                        .append("', null);\n");
                treeIndex++;
                buff.append("setNode(").append(treeIndex).append(levelColumnType)
                        .append(", 'type', '").append(info.type).append("', null);\n");
                treeIndex++;
                buff.append("setNode(").append(treeIndex).append(levelColumnType)
                        .append(", 'type', '")
                        .append(PageParser.escapeJavaScript(info.columns))
                        .append("', null);\n");
                treeIndex++;
            }
        }
        return treeIndex;
    }

    private int addTablesAndViews(DbSchema schema, boolean mainSchema, StringBuilder builder, int treeIndex)
            throws SQLException {
        if (schema == null) {
            return treeIndex;
        }
        Connection conn = session.getConnection();
        DatabaseMetaData meta = session.getMetaData();
        int level = mainSchema ? 0 : 1;
        boolean showColumns = mainSchema || !schema.isSystem;
        String indentation = ", " + level + ", " + (showColumns ? "1" : "2") + ", ";
        String indentNode = ", " + (level + 1) + ", 2, ";
        DbTableOrView[] tables = schema.getTables();
        if (tables == null) {
            return treeIndex;
        }
        DbContents contents = schema.getContents();
        boolean isOracle = contents.isOracle();
        boolean notManyTables = tables.length < SysProperties.CONSOLE_MAX_TABLES_LIST_INDEXES;
        try (PreparedStatement prep = showColumns ? prepareViewDefinitionQuery(conn, contents) : null) {
            if (prep != null) {
                prep.setString(1, schema.name);
            }
            if (schema.isSystem) {
                Arrays.sort(tables, SYSTEM_SCHEMA_COMPARATOR);
                for (DbTableOrView table : tables) {
                    treeIndex = addTableOrView(schema, mainSchema, builder, treeIndex, meta, false, indentation,
                            isOracle, notManyTables, table, table.isView(), prep, indentNode);
                }
            } else {
                for (DbTableOrView table : tables) {
                    if (table.isView()) {
                        continue;
                    }
                    treeIndex = addTableOrView(schema, mainSchema, builder, treeIndex, meta, showColumns, indentation,
                            isOracle, notManyTables, table, false, null, indentNode);
                }
                for (DbTableOrView table : tables) {
                    if (!table.isView()) {
                        continue;
                    }
                    treeIndex = addTableOrView(schema, mainSchema, builder, treeIndex, meta, showColumns, indentation,
                            isOracle, notManyTables, table, true, prep, indentNode);
                }
            }
        }
        return treeIndex;
    }

    private static PreparedStatement prepareViewDefinitionQuery(Connection conn, DbContents contents) {
        if (contents.mayHaveStandardViews()) {
            try {
                return conn.prepareStatement("SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS"
                        + " WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?");
            } catch (SQLException e) {
                contents.setMayHaveStandardViews(false);
            }
        }
        return null;
    }

    private static int addTableOrView(DbSchema schema, boolean mainSchema, StringBuilder builder, int treeIndex,
            DatabaseMetaData meta, boolean showColumns, String indentation, boolean isOracle, boolean notManyTables,
            DbTableOrView table, boolean isView, PreparedStatement prep, String indentNode) throws SQLException {
        int tableId = treeIndex;
        String tab = table.getQuotedName();
        if (!mainSchema) {
            tab = schema.quotedName + '.' + tab;
        }
        tab = escapeIdentifier(tab);
        builder.append("setNode(").append(treeIndex).append(indentation)
                .append(" '").append(isView ? "view" : "table").append("', '")
                .append(PageParser.escapeJavaScript(table.getName()))
                .append("', 'javascript:ins(\\'").append(tab).append("\\',true)');\n");
        treeIndex++;
        if (showColumns) {
            StringBuilder columnsBuilder = new StringBuilder();
            treeIndex = addColumns(mainSchema, table, builder, treeIndex, notManyTables, columnsBuilder);
            if (isView) {
                if (prep != null) {
                    prep.setString(2, table.getName());
                    try (ResultSet rs = prep.executeQuery()) {
                        if (rs.next()) {
                            String sql = rs.getString(1);
                            if (sql != null) {
                                builder.append("setNode(").append(treeIndex).append(indentNode).append(" 'type', '")
                                        .append(PageParser.escapeJavaScript(sql)).append("', null);\n");
                                treeIndex++;
                            }
                        }
                    }
                }
            } else if (!isOracle && notManyTables) {
                treeIndex = addIndexes(mainSchema, meta, table.getName(), schema.name, builder, treeIndex);
            }
            builder.append("addTable('")
                    .append(PageParser.escapeJavaScript(table.getName())).append("', '")
                    .append(PageParser.escapeJavaScript(columnsBuilder.toString())).append("', ")
                    .append(tableId).append(");\n");
        }
        return treeIndex;
    }

    private String tables() {
        DbContents contents = session.getContents();
        boolean isH2 = false;
        try {
            String url = (String) session.get("url");
            Connection conn = session.getConnection();
            contents.readContents(url, conn);
            session.loadBnf();
            isH2 = contents.isH2();

            StringBuilder buff = new StringBuilder()
                    .append("setNode(0, 0, 0, 'database', '")
                    .append(PageParser.escapeJavaScript(url))
                    .append("', null);\n");
            int treeIndex = 1;

            DbSchema defaultSchema = contents.getDefaultSchema();
            treeIndex = addTablesAndViews(defaultSchema, true, buff, treeIndex);
            DbSchema[] schemas = contents.getSchemas();
            for (DbSchema schema : schemas) {
                if (schema == defaultSchema || schema == null) {
                    continue;
                }
                buff.append("setNode(").append(treeIndex).append(", 0, 1, 'folder', '")
                        .append(PageParser.escapeJavaScript(schema.name))
                        .append("', null);\n");
                treeIndex++;
                treeIndex = addTablesAndViews(schema, false, buff, treeIndex);
            }
            if (isH2) {
                try (Statement stat = conn.createStatement()) {
                    ResultSet rs;
                    try {
                        rs = stat.executeQuery("SELECT SEQUENCE_NAME, BASE_VALUE, INCREMENT FROM " +
                                "INFORMATION_SCHEMA.SEQUENCES ORDER BY SEQUENCE_NAME");
                    } catch (SQLException e) {
                        rs = stat.executeQuery("SELECT SEQUENCE_NAME, CURRENT_VALUE, INCREMENT FROM " +
                                "INFORMATION_SCHEMA.SEQUENCES ORDER BY SEQUENCE_NAME");
                    }
                    for (int i = 0; rs.next(); i++) {
                        if (i == 0) {
                            buff.append("setNode(").append(treeIndex)
                                    .append(", 0, 1, 'sequences', '${text.tree.sequences}', null);\n");
                            treeIndex++;
                        }
                        String name = rs.getString(1);
                        String currentBase = rs.getString(2);
                        String increment = rs.getString(3);
                        buff.append("setNode(").append(treeIndex)
                                .append(", 1, 1, 'sequence', '")
                                .append(PageParser.escapeJavaScript(name))
                                .append("', null);\n");
                        treeIndex++;
                        buff.append("setNode(").append(treeIndex)
                                .append(", 2, 2, 'type', '${text.tree.current}: ")
                                .append(PageParser.escapeJavaScript(currentBase))
                                .append("', null);\n");
                        treeIndex++;
                        if (!"1".equals(increment)) {
                            buff.append("setNode(").append(treeIndex)
                                    .append(", 2, 2, 'type', '${text.tree.increment}: ")
                                    .append(PageParser.escapeJavaScript(increment))
                                    .append("', null);\n");
                            treeIndex++;
                        }
                    }
                    rs.close();
                    try {
                        rs = stat.executeQuery(
                                "SELECT USER_NAME, IS_ADMIN FROM INFORMATION_SCHEMA.USERS ORDER BY USER_NAME");
                    } catch (SQLException e) {
                        rs = stat.executeQuery("SELECT NAME, ADMIN FROM INFORMATION_SCHEMA.USERS ORDER BY NAME");
                    }
                    for (int i = 0; rs.next(); i++) {
                        if (i == 0) {
                            buff.append("setNode(").append(treeIndex)
                                    .append(", 0, 1, 'users', '${text.tree.users}', null);\n");
                            treeIndex++;
                        }
                        String name = rs.getString(1);
                        String admin = rs.getString(2);
                        buff.append("setNode(").append(treeIndex)
                                .append(", 1, 1, 'user', '")
                                .append(PageParser.escapeJavaScript(name))
                                .append("', null);\n");
                        treeIndex++;
                        if (admin.equalsIgnoreCase("TRUE")) {
                            buff.append("setNode(").append(treeIndex)
                                    .append(", 2, 2, 'type', '${text.tree.admin}', null);\n");
                            treeIndex++;
                        }
                    }
                    rs.close();
                }
            }
            DatabaseMetaData meta = session.getMetaData();
            String version = meta.getDatabaseProductName() + " " +
                    meta.getDatabaseProductVersion();
            buff.append("setNode(").append(treeIndex)
                    .append(", 0, 0, 'info', '")
                    .append(PageParser.escapeJavaScript(version))
                    .append("', null);\n")
                    .append("refreshQueryTables();");
            session.put("tree", buff.toString());
        } catch (Exception e) {
            session.put("tree", "");
            session.put("error", getStackTrace(0, e, isH2));
        }
        return "tables.jsp";
    }

    private String getStackTrace(int id, Throwable e, boolean isH2) {
        try {
            StringWriter writer = new StringWriter();
            e.printStackTrace(new PrintWriter(writer));
            String stackTrace = writer.toString();
            stackTrace = PageParser.escapeHtml(stackTrace);
            if (isH2) {
                stackTrace = linkToSource(stackTrace);
            }
            stackTrace = StringUtils.replaceAll(stackTrace, "\t",
                    "&nbsp;&nbsp;&nbsp;&nbsp;");
            String message = PageParser.escapeHtml(e.getMessage());
            String error = "<a class=\"error\" href=\"#\" " +
                    "onclick=\"var x=document.getElementById('st" + id +
                    "').style;x.display=x.display==''?'none':'';\">" + message +
                    "</a>";
            if (e instanceof SQLException) {
                SQLException se = (SQLException) e;
                error += " " + se.getSQLState() + "/" + se.getErrorCode();
                if (isH2) {
                    int code = se.getErrorCode();
                    error += " <a href=\"https://h2database.com/javadoc/" +
                            "org/h2/api/ErrorCode.html#c" + code +
                            "\">(${text.a.help})</a>";
                }
            }
            error += "<span style=\"display: none;\" id=\"st" + id +
                    "\"><br />" + stackTrace + "</span>";
            error = formatAsError(error);
            return error;
        } catch (OutOfMemoryError e2) {
            server.traceError(e);
            return e.toString();
        }
    }

    private static String linkToSource(String s) {
        try {
            StringBuilder result = new StringBuilder(s.length());
            int idx = s.indexOf("<br />");
            result.append(s, 0, idx);
            while (true) {
                int start = s.indexOf("org.h2.", idx);
                if (start < 0) {
                    result.append(s.substring(idx));
                    break;
                }
                result.append(s, idx, start);
                int end = s.indexOf(')', start);
                if (end < 0) {
                    result.append(s.substring(idx));
                    break;
                }
                String element = s.substring(start, end);
                int open = element.lastIndexOf('(');
                int dotMethod = element.lastIndexOf('.', open - 1);
                int dotClass = element.lastIndexOf('.', dotMethod - 1);
                String packageName = element.substring(0, dotClass);
                int colon = element.lastIndexOf(':');
                String file = element.substring(open + 1, colon);
                String lineNumber = element.substring(colon + 1, element.length());
                String fullFileName = packageName.replace('.', '/') + "/" + file;
                result.append("<a href=\"https://h2database.com/html/source.html?file=");
                result.append(fullFileName);
                result.append("&line=");
                result.append(lineNumber);
                result.append("&build=");
                result.append(Constants.BUILD_ID);
                result.append("\">");
                result.append(element);
                result.append("</a>");
                idx = end;
            }
            return result.toString();
        } catch (Throwable t) {
            return s;
        }
    }

    private static String formatAsError(String s) {
        return "<div class=\"error\">" + s + "</div>";
    }

    private String test(NetworkConnectionInfo networkConnectionInfo) {
        String driver = attributes.getProperty("driver", "");
        String url = attributes.getProperty("url", "");
        String user = attributes.getProperty("user", "");
        String password = attributes.getProperty("password", "");
        session.put("driver", driver);
        session.put("url", url);
        session.put("user", user);
        boolean isH2 = url.startsWith("jdbc:h2:");
        try {
            long start = System.currentTimeMillis();
            String profOpen = "", profClose = "";
            Profiler prof = new Profiler();
            prof.startCollecting();
            Connection conn;
            try {
                conn = server.getConnection(driver, url, user, password, null, networkConnectionInfo);
            } finally {
                prof.stopCollecting();
                profOpen = prof.getTop(3);
            }
            prof = new Profiler();
            prof.startCollecting();
            try {
                JdbcUtils.closeSilently(conn);
            } finally {
                prof.stopCollecting();
                profClose = prof.getTop(3);
            }
            long time = System.currentTimeMillis() - start;
            String success;
            if (time > 1000) {
                success = "<a class=\"error\" href=\"#\" " +
                    "onclick=\"var x=document.getElementById('prof').style;x." +
                    "display=x.display==''?'none':'';\">" +
                    "${text.login.testSuccessful}</a>" +
                    "<span style=\"display: none;\" id=\"prof\"><br />" +
                    PageParser.escapeHtml(profOpen) +
                    "<br />" +
                    PageParser.escapeHtml(profClose) +
                    "</span>";
            } else {
                success = "<div class=\"success\">${text.login.testSuccessful}</div>";
            }
            session.put("error", success);
            // session.put("error", "${text.login.testSuccessful}");
            return "login.jsp";
        } catch (Exception e) {
            session.put("error", getLoginError(e, isH2));
            return "login.jsp";
        }
    }

    /**
     * Get the formatted login error message.
     *
     * @param e the exception
     * @param isH2 if the current database is a H2 database
     * @return the formatted error message
     */
    private String getLoginError(Exception e, boolean isH2) {
        if (e instanceof JdbcException && ((JdbcException) e).getErrorCode() == ErrorCode.CLASS_NOT_FOUND_1) {
            return "${text.login.driverNotFound}<br />" + getStackTrace(0, e, isH2);
        }
        return getStackTrace(0, e, isH2);
    }

    private String login(NetworkConnectionInfo networkConnectionInfo) {
        String driver = attributes.getProperty("driver", "");
        String url = attributes.getProperty("url", "");
        String user = attributes.getProperty("user", "");
        String password = attributes.getProperty("password", "");
        session.put("autoCommit", "checked");
        session.put("autoComplete", "1");
        session.put("maxrows", "1000");
        boolean isH2 = url.startsWith("jdbc:h2:");
        try {
            Connection conn = server.getConnection(driver, url, user, password, (String) session.get("key"),
                    networkConnectionInfo);
            session.setConnection(conn);
            session.put("url", url);
            session.put("user", user);
            session.remove("error");
            settingSave();
            return "frame.jsp";
        } catch (Exception e) {
            session.put("error", getLoginError(e, isH2));
            return "login.jsp";
        }
    }

    private String logout() {
        try {
            Connection conn = session.getConnection();
            session.setConnection(null);
            session.remove("conn");
            session.remove("result");
            session.remove("tables");
            session.remove("user");
            session.remove("tool");
            if (conn != null) {
                if (session.getShutdownServerOnDisconnect()) {
                    server.shutdown();
                } else {
                    conn.close();
                }
            }
        } catch (Exception e) {
            trace(e.toString());
        }
        session.remove("admin");
        return "index.do";
    }

    private String query() {
        String sql = attributes.getProperty("sql").trim();
        try {
            ScriptReader r = new ScriptReader(new StringReader(sql));
            final ArrayList<String> list = new ArrayList<>();
            while (true) {
                String s = r.readStatement();
                if (s == null) {
                    break;
                }
                list.add(s);
            }
            final Connection conn = session.getConnection();
            if (SysProperties.CONSOLE_STREAM && server.getAllowChunked()) {
                String page = new String(server.getFile("result.jsp"), StandardCharsets.UTF_8);
                int idx = page.indexOf("${result}");
                // the first element of the list is the header, the last the
                // footer
                list.add(0, page.substring(0, idx));
                list.add(page.substring(idx + "${result}".length()));
                session.put("chunks", new Iterator<String>() {
                    private int i;
                    @Override
                    public boolean hasNext() {
                        return i < list.size();
                    }
                    @Override
                    public String next() {
                        String s = list.get(i++);
                        if (i == 1 || i == list.size()) {
                            return s;
                        }
                        StringBuilder b = new StringBuilder();
                        query(conn, s, i - 1, list.size() - 2, b);
                        return b.toString();
                    }
                });
                return "result.jsp";
            }
            String result;
            StringBuilder buff = new StringBuilder();
            for (int i = 0; i < list.size(); i++) {
                String s = list.get(i);
                query(conn, s, i, list.size(), buff);
            }
            result = buff.toString();
            session.put("result", result);
        } catch (Throwable e) {
            session.put("result", getStackTrace(0, e, session.getContents().isH2()));
        }
        return "result.jsp";
    }

    /**
     * Execute a query and append the result to the buffer.
     *
     * @param conn the connection
     * @param s the statement
     * @param i the index
     * @param size the number of statements
     * @param buff the target buffer
     */
    void query(Connection conn, String s, int i, int size, StringBuilder buff) {
        if (!(s.startsWith("@") && s.endsWith("."))) {
            buff.append(PageParser.escapeHtml(s + ";")).append("<br />");
        }
        boolean forceEdit = s.startsWith("@edit");
        buff.append(getResult(conn, i + 1, s, size == 1, forceEdit)).
            append("<br />");
    }

    private String editResult() {
        ResultSet rs = session.result;
        int row = Integer.parseInt(attributes.getProperty("row"));
        int op = Integer.parseInt(attributes.getProperty("op"));
        String result = "", error = "";
        try {
            if (op == 1) {
                boolean insert = row < 0;
                if (insert) {
                    rs.moveToInsertRow();
                } else {
                    rs.absolute(row);
                }
                for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                    String x = attributes.getProperty("r" + row + "c" + (i + 1));
                    unescapeData(x, rs, i + 1);
                }
                if (insert) {
                    rs.insertRow();
                } else {
                    rs.updateRow();
                }
            } else if (op == 2) {
                rs.absolute(row);
                rs.deleteRow();
            } else if (op == 3) {
                // cancel
            }
        } catch (Throwable e) {
            result = "<br />" + getStackTrace(0, e, session.getContents().isH2());
            error = formatAsError(e.getMessage());
        }
        String sql = "@edit " + (String) session.get("resultSetSQL");
        Connection conn = session.getConnection();
        result = error + getResult(conn, -1, sql, true, true) + result;
        session.put("result", result);
        return "result.jsp";
    }

    private int getMaxrows() {
        String r = (String) session.get("maxrows");
        return r == null ? 0 : Integer.parseInt(r);
    }

    private String getResult(Connection conn, int id, String sql,
            boolean allowEdit, boolean forceEdit) {
        try {
            sql = sql.trim();
            StringBuilder buff = new StringBuilder();
            String sqlUpper = StringUtils.toUpperEnglish(sql);
            if (sqlUpper.contains("CREATE") ||
                    sqlUpper.contains("DROP") ||
                    sqlUpper.contains("ALTER") ||
                    sqlUpper.contains("RUNSCRIPT")) {
                String sessionId = attributes.getProperty("jsessionid");
                buff.append("<script type=\"text/javascript\">parent['h2menu'].location='tables.do?jsessionid=")
                        .append(sessionId).append("';</script>");
            }
            Statement stat;
            DbContents contents = session.getContents();
            if (forceEdit || (allowEdit && contents.isH2())) {
                stat = conn.createStatement(
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
            } else {
                stat = conn.createStatement();
            }
            ResultSet rs;
            long time = System.currentTimeMillis();
            boolean metadata = false;
            Object generatedKeys = null;
            boolean edit = false;
            boolean list = false;
            if (JdbcUtils.isBuiltIn(sql, "@autocommit_true")) {
                conn.setAutoCommit(true);
                return "${text.result.autoCommitOn}";
            } else if (JdbcUtils.isBuiltIn(sql, "@autocommit_false")) {
                conn.setAutoCommit(false);
                return "${text.result.autoCommitOff}";
            } else if (JdbcUtils.isBuiltIn(sql, "@cancel")) {
                stat = session.executingStatement;
                if (stat != null) {
                    stat.cancel();
                    buff.append("${text.result.statementWasCanceled}");
                } else {
                    buff.append("${text.result.noRunningStatement}");
                }
                return buff.toString();
            } else if (JdbcUtils.isBuiltIn(sql, "@edit")) {
                edit = true;
                sql = StringUtils.trimSubstring(sql, "@edit".length());
                session.put("resultSetSQL", sql);
            }
            if (JdbcUtils.isBuiltIn(sql, "@list")) {
                list = true;
                sql = StringUtils.trimSubstring(sql, "@list".length());
            }
            if (JdbcUtils.isBuiltIn(sql, "@meta")) {
                metadata = true;
                sql = StringUtils.trimSubstring(sql, "@meta".length());
            }
            if (JdbcUtils.isBuiltIn(sql, "@generated")) {
                generatedKeys = true;
                int offset = "@generated".length();
                int length = sql.length();
                for (; offset < length; offset++) {
                    char c = sql.charAt(offset);
                    if (c == '(') {
                        Parser p = new Parser();
                        generatedKeys = p.parseColumnList(sql, offset);
                        offset = p.getLastParseIndex();
                        break;
                    }
                    if (!Character.isWhitespace(c)) {
                        break;
                    }
                }
                sql = StringUtils.trimSubstring(sql, offset);
            } else if (JdbcUtils.isBuiltIn(sql, "@history")) {
                buff.append(getCommandHistoryString());
                return buff.toString();
            } else if (JdbcUtils.isBuiltIn(sql, "@loop")) {
                sql = StringUtils.trimSubstring(sql, "@loop".length());
                int idx = sql.indexOf(' ');
                int count = Integer.decode(sql.substring(0, idx));
                sql = StringUtils.trimSubstring(sql, idx);
                return executeLoop(conn, count, sql);
            } else if (JdbcUtils.isBuiltIn(sql, "@maxrows")) {
                int maxrows = (int) Double.parseDouble(StringUtils.trimSubstring(sql, "@maxrows".length()));
                session.put("maxrows", Integer.toString(maxrows));
                return "${text.result.maxrowsSet}";
            } else if (JdbcUtils.isBuiltIn(sql, "@parameter_meta")) {
                sql = StringUtils.trimSubstring(sql, "@parameter_meta".length());
                PreparedStatement prep = conn.prepareStatement(sql);
                buff.append(getParameterResultSet(prep.getParameterMetaData()));
                return buff.toString();
            } else if (JdbcUtils.isBuiltIn(sql, "@password_hash")) {
                sql = StringUtils.trimSubstring(sql, "@password_hash".length());
                String[] p = JdbcUtils.split(sql);
                return StringUtils.convertBytesToHex(
                        SHA256.getKeyPasswordHash(p[0], p[1].toCharArray()));
            } else if (JdbcUtils.isBuiltIn(sql, "@prof_start")) {
                if (profiler != null) {
                    profiler.stopCollecting();
                }
                profiler = new Profiler();
                profiler.startCollecting();
                return "Ok";
            } else if (JdbcUtils.isBuiltIn(sql, "@sleep")) {
                String s = StringUtils.trimSubstring(sql, "@sleep".length());
                int sleep = 1;
                if (s.length() > 0) {
                    sleep = Integer.parseInt(s);
                }
                Thread.sleep(sleep * 1000);
                return "Ok";
            } else if (JdbcUtils.isBuiltIn(sql, "@transaction_isolation")) {
                String s = StringUtils.trimSubstring(sql, "@transaction_isolation".length());
                if (s.length() > 0) {
                    int level = Integer.parseInt(s);
                    conn.setTransactionIsolation(level);
                }
                buff.append("Transaction Isolation: ")
                        .append(conn.getTransactionIsolation())
                        .append("<br />");
                buff.append(Connection.TRANSACTION_READ_UNCOMMITTED)
                        .append(": read_uncommitted<br />");
                buff.append(Connection.TRANSACTION_READ_COMMITTED)
                        .append(": read_committed<br />");
                buff.append(Connection.TRANSACTION_REPEATABLE_READ)
                        .append(": repeatable_read<br />");
                buff.append(Constants.TRANSACTION_SNAPSHOT)
                        .append(": snapshot<br />");
                buff.append(Connection.TRANSACTION_SERIALIZABLE)
                        .append(": serializable");
            }
            if (sql.startsWith("@")) {
                rs = JdbcUtils.getMetaResultSet(conn, sql);
                if (rs == null && JdbcUtils.isBuiltIn(sql, "@prof_stop")) {
                    if (profiler != null) {
                        profiler.stopCollecting();
                        SimpleResultSet simple = new SimpleResultSet();
                        simple.addColumn("Top Stack Trace(s)", Types.VARCHAR, 0, 0);
                        simple.addRow(profiler.getTop(3));
                        rs = simple;
                        profiler = null;
                    }
                }
                if (rs == null) {
                    buff.append("?: ").append(sql);
                    return buff.toString();
                }
            } else {
                int maxrows = getMaxrows();
                stat.setMaxRows(maxrows);
                session.executingStatement = stat;
                boolean isResultSet;
                if (generatedKeys == null) {
                    isResultSet = stat.execute(sql);
                } else if (generatedKeys instanceof Boolean) {
                    isResultSet = stat.execute(sql,
                            ((Boolean) generatedKeys) ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS);
                } else if (generatedKeys instanceof String[]) {
                    isResultSet = stat.execute(sql, (String[]) generatedKeys);
                } else {
                    isResultSet = stat.execute(sql, (int[]) generatedKeys);
                }
                session.addCommand(sql);
                if (generatedKeys != null) {
                    rs = null;
                    rs = stat.getGeneratedKeys();
                } else {
                    if (!isResultSet) {
                        long updateCount;
                        try {
                            updateCount = stat.getLargeUpdateCount();
                        } catch (UnsupportedOperationException e) {
                            updateCount = stat.getUpdateCount();
                        }
                        buff.append("${text.result.updateCount}: ").append(updateCount);
                        time = System.currentTimeMillis() - time;
                        buff.append("<br />(").append(time).append(" ms)");
                        stat.close();
                        return buff.toString();
                    }
                    rs = stat.getResultSet();
                }
            }
            time = System.currentTimeMillis() - time;
            buff.append(getResultSet(sql, rs, metadata, list, edit, time, allowEdit));
            // SQLWarning warning = stat.getWarnings();
            // if (warning != null) {
            // buff.append("<br />Warning:<br />").
            // append(getStackTrace(id, warning));
            // }
            if (!edit) {
                stat.close();
            }
            return buff.toString();
        } catch (Throwable e) {
            // throwable: including OutOfMemoryError and so on
            return getStackTrace(id, e, session.getContents().isH2());
        } finally {
            session.executingStatement = null;
        }
    }

    private String executeLoop(Connection conn, int count, String sql)
            throws SQLException {
        ArrayList<Integer> params = new ArrayList<>();
        int idx = 0;
        while (!stop) {
            idx = sql.indexOf('?', idx);
            if (idx < 0) {
                break;
            }
            if (JdbcUtils.isBuiltIn(sql.substring(idx), "?/*rnd*/")) {
                params.add(1);
                sql = sql.substring(0, idx) + "?" + sql.substring(idx + "/*rnd*/".length() + 1);
            } else {
                params.add(0);
            }
            idx++;
        }
        boolean prepared;
        Random random = new Random(1);
        long time = System.currentTimeMillis();
        if (JdbcUtils.isBuiltIn(sql, "@statement")) {
            sql = StringUtils.trimSubstring(sql, "@statement".length());
            prepared = false;
            Statement stat = conn.createStatement();
            for (int i = 0; !stop && i < count; i++) {
                String s = sql;
                for (Integer type : params) {
                    idx = s.indexOf('?');
                    if (type == 1) {
                        s = s.substring(0, idx) + random.nextInt(count) + s.substring(idx + 1);
                    } else {
                        s = s.substring(0, idx) + i + s.substring(idx + 1);
                    }
                }
                if (stat.execute(s)) {
                    ResultSet rs = stat.getResultSet();
                    while (!stop && rs.next()) {
                        // maybe get the data as well
                    }
                    rs.close();
                }
            }
        } else {
            prepared = true;
            PreparedStatement prep = conn.prepareStatement(sql);
            for (int i = 0; !stop && i < count; i++) {
                for (int j = 0; j < params.size(); j++) {
                    Integer type = params.get(j);
                    if (type == 1) {
                        prep.setInt(j + 1, random.nextInt(count));
                    } else {
                        prep.setInt(j + 1, i);
                    }
                }
                if (session.getContents().isSQLite()) {
                    // SQLite currently throws an exception on prep.execute()
                    prep.executeUpdate();
                } else {
                    if (prep.execute()) {
                        ResultSet rs = prep.getResultSet();
                        while (!stop && rs.next()) {
                            // maybe get the data as well
                        }
                        rs.close();
                    }
                }
            }
        }
        time = System.currentTimeMillis() - time;
        StringBuilder builder = new StringBuilder().append(time).append(" ms: ").append(count).append(" * ")
                .append(prepared ? "(Prepared) " : "(Statement) ").append('(');
        for (int i = 0, size = params.size(); i < size; i++) {
            if (i > 0) {
                builder.append(", ");
            }
            builder.append(params.get(i) == 0 ? "i" : "rnd");
        }
        return builder.append(") ").append(sql).toString();
    }

    private String getCommandHistoryString() {
        StringBuilder buff = new StringBuilder();
        ArrayList<String> history = session.getCommandHistory();
        buff.append("<table cellspacing=0 cellpadding=0>" +
                "<tr><th></th><th>Command</th></tr>");
        for (int i = history.size() - 1; i >= 0; i--) {
            String sql = history.get(i);
            buff.append("<tr><td><a href=\"getHistory.do?id=").
                append(i).
                append("&jsessionid=${sessionId}\" target=\"h2query\" >").
                append("<img width=16 height=16 src=\"ico_write.gif\" " +
                        "onmouseover = \"this.className ='icon_hover'\" ").
                append("onmouseout = \"this.className ='icon'\" " +
                        "class=\"icon\" alt=\"${text.resultEdit.edit}\" ").
                append("title=\"${text.resultEdit.edit}\" border=\"1\"/></a>").
                append("</td><td>").
                append(PageParser.escapeHtml(sql)).
                append("</td></tr>");
        }
        buff.append("</table>");
        return buff.toString();
    }

    private static String getParameterResultSet(ParameterMetaData meta)
            throws SQLException {
        StringBuilder buff = new StringBuilder();
        if (meta == null) {
            return "No parameter meta data";
        }
        buff.append("<table cellspacing=0 cellpadding=0>").
            append("<tr><th>className</th><th>mode</th><th>type</th>").
            append("<th>typeName</th><th>precision</th><th>scale</th></tr>");
        for (int i = 0; i < meta.getParameterCount(); i++) {
            buff.append("</tr><td>").
                append(meta.getParameterClassName(i + 1)).
                append("</td><td>").
                append(meta.getParameterMode(i + 1)).
                append("</td><td>").
                append(meta.getParameterType(i + 1)).
                append("</td><td>").
                append(meta.getParameterTypeName(i + 1)).
                append("</td><td>").
                append(meta.getPrecision(i + 1)).
                append("</td><td>").
                append(meta.getScale(i + 1)).
                append("</td></tr>");
        }
        buff.append("</table>");
        return buff.toString();
    }

    private String getResultSet(String sql, ResultSet rs, boolean metadata,
            boolean list, boolean edit, long time, boolean allowEdit)
            throws SQLException {
        int maxrows = getMaxrows();
        time = System.currentTimeMillis() - time;
        StringBuilder buff = new StringBuilder();
        if (edit) {
            buff.append("<form id=\"editing\" name=\"editing\" method=\"post\" " +
                    "action=\"editResult.do?jsessionid=${sessionId}\" " +
                    "id=\"mainForm\" target=\"h2result\">" +
                    "<input type=\"hidden\" name=\"op\" value=\"1\" />" +
                    "<input type=\"hidden\" name=\"row\" value=\"\" />" +
                    "<table class=\"resultSet\" cellspacing=\"0\" cellpadding=\"0\" id=\"editTable\">");
        } else {
            buff.append("<table class=\"resultSet\" cellspacing=\"0\" cellpadding=\"0\">");
        }
        if (metadata) {
            SimpleResultSet r = new SimpleResultSet();
            r.addColumn("#", Types.INTEGER, 0, 0);
            r.addColumn("label", Types.VARCHAR, 0, 0);
            r.addColumn("catalog", Types.VARCHAR, 0, 0);
            r.addColumn("schema", Types.VARCHAR, 0, 0);
            r.addColumn("table", Types.VARCHAR, 0, 0);
            r.addColumn("column", Types.VARCHAR, 0, 0);
            r.addColumn("type", Types.INTEGER, 0, 0);
            r.addColumn("typeName", Types.VARCHAR, 0, 0);
            r.addColumn("class", Types.VARCHAR, 0, 0);
            r.addColumn("precision", Types.INTEGER, 0, 0);
            r.addColumn("scale", Types.INTEGER, 0, 0);
            r.addColumn("displaySize", Types.INTEGER, 0, 0);
            r.addColumn("autoIncrement", Types.BOOLEAN, 0, 0);
            r.addColumn("caseSensitive", Types.BOOLEAN, 0, 0);
            r.addColumn("currency", Types.BOOLEAN, 0, 0);
            r.addColumn("nullable", Types.INTEGER, 0, 0);
            r.addColumn("readOnly", Types.BOOLEAN, 0, 0);
            r.addColumn("searchable", Types.BOOLEAN, 0, 0);
            r.addColumn("signed", Types.BOOLEAN, 0, 0);
            r.addColumn("writable", Types.BOOLEAN, 0, 0);
            r.addColumn("definitelyWritable", Types.BOOLEAN, 0, 0);
            ResultSetMetaData m = rs.getMetaData();
            for (int i = 1; i <= m.getColumnCount(); i++) {
                r.addRow(i,
                        m.getColumnLabel(i),
                        m.getCatalogName(i),
                        m.getSchemaName(i),
                        m.getTableName(i),
                        m.getColumnName(i),
                        m.getColumnType(i),
                        m.getColumnTypeName(i),
                        m.getColumnClassName(i),
                        m.getPrecision(i),
                        m.getScale(i),
                        m.getColumnDisplaySize(i),
                        m.isAutoIncrement(i),
                        m.isCaseSensitive(i),
                        m.isCurrency(i),
                        m.isNullable(i),
                        m.isReadOnly(i),
                        m.isSearchable(i),
                        m.isSigned(i),
                        m.isWritable(i),
                        m.isDefinitelyWritable(i));
            }
            rs = r;
        }
        ResultSetMetaData meta = rs.getMetaData();
        int columns = meta.getColumnCount();
        int rows = 0;
        if (list) {
            buff.append("<tr><th>Column</th><th>Data</th></tr><tr>");
            while (rs.next()) {
                if (maxrows > 0 && rows >= maxrows) {
                    break;
                }
                rows++;
                buff.append("<tr><td>Row #</td><td>").
                    append(rows).append("</tr>");
                for (int i = 0; i < columns; i++) {
                    buff.append("<tr><td>").
                        append(PageParser.escapeHtml(meta.getColumnLabel(i + 1))).
                        append("</td><td>").
                        append(escapeData(rs, i + 1)).
                        append("</td></tr>");
                }
            }
        } else {
            buff.append("<tr>");
            if (edit) {
                buff.append("<th>${text.resultEdit.action}</th>");
            }
            for (int i = 0; i < columns; i++) {
                buff.append("<th>").
                    append(PageParser.escapeHtml(meta.getColumnLabel(i + 1))).
                    append("</th>");
            }
            buff.append("</tr>");
            while (rs.next()) {
                if (maxrows > 0 && rows >= maxrows) {
                    break;
                }
                rows++;
                buff.append("<tr>");
                if (edit) {
                    buff.append("<td>").
                        append("<img onclick=\"javascript:editRow(").
                        append(rs.getRow()).
                        append(",'${sessionId}', '${text.resultEdit.save}', " +
                                "'${text.resultEdit.cancel}'").
                        append(")\" width=16 height=16 src=\"ico_write.gif\" " +
                                "onmouseover = \"this.className ='icon_hover'\" " +
                                "onmouseout = \"this.className ='icon'\" " +
                                "class=\"icon\" alt=\"${text.resultEdit.edit}\" " +
                                "title=\"${text.resultEdit.edit}\" border=\"1\"/>").
                        append("<img onclick=\"javascript:deleteRow(").
                        append(rs.getRow()).
                        append(",'${sessionId}', '${text.resultEdit.delete}', " +
                                "'${text.resultEdit.cancel}'").
                        append(")\" width=16 height=16 src=\"ico_remove.gif\" " +
                                "onmouseover = \"this.className ='icon_hover'\" " +
                                "onmouseout = \"this.className ='icon'\" " +
                                "class=\"icon\" alt=\"${text.resultEdit.delete}\" " +
                                "title=\"${text.resultEdit.delete}\" border=\"1\" /></a>").
                        append("</td>");
                }
                for (int i = 0; i < columns; i++) {
                    buff.append("<td>").
                        append(escapeData(rs, i + 1)).
                        append("</td>");
                }
                buff.append("</tr>");
            }
        }
        boolean isUpdatable = false;
        try {
            if (!session.getContents().isDB2()) {
                isUpdatable = rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE
                    && rs.getType() != ResultSet.TYPE_FORWARD_ONLY;
            }
        } catch (NullPointerException e) {
            // ignore
            // workaround for a JDBC-ODBC bridge problem
        }
        if (edit) {
            ResultSet old = session.result;
            if (old != null) {
                old.close();
            }
            session.result = rs;
        } else {
            rs.close();
        }
        if (edit) {
            buff.append("<tr><td>").
                append("<img onclick=\"javascript:editRow(-1, " +
                        "'${sessionId}', '${text.resultEdit.save}', '${text.resultEdit.cancel}'").
                append(")\" width=16 height=16 src=\"ico_add.gif\" " +
                        "onmouseover = \"this.className ='icon_hover'\" " +
                        "onmouseout = \"this.className ='icon'\" " +
                        "class=\"icon\" alt=\"${text.resultEdit.add}\" " +
                        "title=\"${text.resultEdit.add}\" border=\"1\"/>").
                append("</td>");
            for (int i = 0; i < columns; i++) {
                buff.append("<td></td>");
            }
            buff.append("</tr>");
        }
        buff.append("</table>");
        if (edit) {
            buff.append("</form>");
        }
        if (rows == 0) {
            buff.append("(${text.result.noRows}");
        } else if (rows == 1) {
            buff.append("(${text.result.1row}");
        } else {
            buff.append('(').append(rows).append(" ${text.result.rows}");
        }
        buff.append(", ");
        time = System.currentTimeMillis() - time;
        buff.append(time).append(" ms)");
        if (!edit && isUpdatable && allowEdit) {
            buff.append("<br /><br />" +
                    "<form name=\"editResult\" method=\"post\" " +
                    "action=\"query.do?jsessionid=${sessionId}\" target=\"h2result\">" +
                    "<input type=\"submit\" class=\"button\" " +
                    "value=\"${text.resultEdit.editResult}\" />" +
                    "<input type=\"hidden\" name=\"sql\" value=\"@edit ").
            append(PageParser.escapeHtmlData(sql)).
            append("\" /></form>");
        }
        return buff.toString();
    }

    /**
     * Save the current connection settings to the properties file.
     *
     * @return the file to open afterwards
     */
    private String settingSave() {
        ConnectionInfo info = new ConnectionInfo();
        info.name = attributes.getProperty("name", "");
        info.driver = attributes.getProperty("driver", "");
        info.url = attributes.getProperty("url", "");
        info.user = attributes.getProperty("user", "");
        server.updateSetting(info);
        attributes.put("setting", info.name);
        server.saveProperties(null);
        return "index.do";
    }

    private static String escapeData(ResultSet rs, int columnIndex) throws SQLException {
        if (DataType.isBinaryColumn(rs.getMetaData(), columnIndex)) {
            byte[] d = rs.getBytes(columnIndex);
            if (d == null) {
                return "<i>null</i>";
            } else if (d.length > 50_000) {
                return "<div style='display: none'>=+</div>" + StringUtils.convertBytesToHex(d, 3) + "... ("
                        + d.length + " ${text.result.bytes})";
            }
            return StringUtils.convertBytesToHex(d);
        }
        String d = rs.getString(columnIndex);
        if (d == null) {
            return "<i>null</i>";
        } else if (d.length() > 100_000) {
            return "<div style='display: none'>=+</div>" + PageParser.escapeHtml(d.substring(0, 100)) + "... ("
                    + d.length() + " ${text.result.characters})";
        } else if (d.equals("null") || d.startsWith("= ") || d.startsWith("=+")) {
            return "<div style='display: none'>= </div>" + PageParser.escapeHtml(d);
        } else if (d.equals("")) {
            // PageParser.escapeHtml replaces "" with a non-breaking space
            return "";
        }
        return PageParser.escapeHtml(d);
    }

    private void unescapeData(String x, ResultSet rs, int columnIndex)
            throws SQLException {
        if (x.equals("null")) {
            rs.updateNull(columnIndex);
            return;
        } else if (x.startsWith("=+")) {
            // don't update
            return;
        } else if (x.equals("=*")) {
            // set an appropriate default value
            int type = rs.getMetaData().getColumnType(columnIndex);
            switch (type) {
            case Types.TIME:
                rs.updateString(columnIndex, "12:00:00");
                break;
            case Types.TIMESTAMP:
            case Types.DATE:
                rs.updateString(columnIndex, "2001-01-01");
                break;
            default:
                rs.updateString(columnIndex, "1");
                break;
            }
            return;
        } else if (x.startsWith("= ")) {
            x = x.substring(2);
        }
        ResultSetMetaData meta = rs.getMetaData();
        if (DataType.isBinaryColumn(meta, columnIndex)) {
            rs.updateBytes(columnIndex, StringUtils.convertHexToBytes(x));
            return;
        }
        int type = meta.getColumnType(columnIndex);
        if (session.getContents().isH2()) {
            rs.updateString(columnIndex, x);
            return;
        }
        switch (type) {
        case Types.BIGINT:
            rs.updateLong(columnIndex, Long.decode(x));
            break;
        case Types.DECIMAL:
            rs.updateBigDecimal(columnIndex, new BigDecimal(x));
            break;
        case Types.DOUBLE:
        case Types.FLOAT:
            rs.updateDouble(columnIndex, Double.parseDouble(x));
            break;
        case Types.REAL:
            rs.updateFloat(columnIndex, Float.parseFloat(x));
            break;
        case Types.INTEGER:
            rs.updateInt(columnIndex, Integer.decode(x));
            break;
        case Types.TINYINT:
            rs.updateShort(columnIndex, Short.decode(x));
            break;
        default:
            rs.updateString(columnIndex, x);
        }
    }

    private String settingRemove() {
        String setting = attributes.getProperty("name", "");
        server.removeSetting(setting);
        ArrayList<ConnectionInfo> settings = server.getSettings();
        if (!settings.isEmpty()) {
            attributes.put("setting", settings.get(0));
        }
        server.saveProperties(null);
        return "index.do";
    }

    /**
     * Get the current mime type.
     *
     * @return the mime type
     */
    String getMimeType() {
        return mimeType;
    }

    boolean getCache() {
        return cache;
    }

    WebSession getSession() {
        return session;
    }

    private void trace(String s) {
        server.trace(s);
    }

}
